package cn.edu.ncu.housinghub.mapper;

import cn.edu.ncu.housinghub.dto.request.house.HouseConditionRequest;
import cn.edu.ncu.housinghub.dto.request.house.NearbyHouseRequest;
import cn.edu.ncu.housinghub.entity.agent.AgentHouseApplication;
import cn.edu.ncu.housinghub.entity.house.ComplementaryFacilities;
import cn.edu.ncu.housinghub.entity.house.PropertyVerification;
import cn.edu.ncu.housinghub.mapper.provider.HouseSqlProvider;
//import cn.edu.ncu.housinghub.dto.request.house.HouseConditionRequest;
import cn.edu.ncu.housinghub.entity.house.House;
import cn.edu.ncu.housinghub.entity.house.PropertyVerification;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Set;

@Mapper
public interface HouseMapper {

    /**
     * 查询所有房源信息
     */
    @Select("SELECT houseId, " +
            "landLordId, " +
            "houseSummary, " +
            "agentId, " +
            "complementaryFacilityId, " +
            "propertyVerifyId, " +
            "houseLocation, " +
            "layout, " +
            "FLOOR, " +
            "orientation, " +
            "STATUS, " +
            "rent, " +
            "paymentType, " +
            "houseRentType, " +
            "subwayInfo, " +
            "createTime, " +
            "isDeleted " +
            "FROM house " +
            "order by houseId")
    List<House> selectAllHouses ();

    /**
     * 条件查询房源信息
     */
    @SelectProvider(type = HouseSqlProvider.class, method = "provideSelectHousesByCondition")
    List<House> selectHousesByCondition (HouseConditionRequest condition);

    /**
     * 查询附近房源信息
     */
    @SelectProvider(type = HouseSqlProvider.class, method = "provideSelectNearbyHouses")
    List<House> selectNearbyHouses (NearbyHouseRequest request);

    /**
     * 分页查询附近房源数量
     */
    @SelectProvider(type = HouseSqlProvider.class, method = "provideCountNearbyHouses")
    Long countNearbyHouses(NearbyHouseRequest request);

    @Select("SELECT " +
            "h.houseId, h.landLordId, h.houseSummary, h.houseTitle, h.agentId, h.complementaryFacilityId, " +
            "h.propertyVerifyId, h.houseLocation, h.layout, h.area, h.depositType, h.floor, " +
            "h.orientation, h.status, h.rent, h.paymentType, h.houseRentType, " +
            "h.subwayInfo, h.createTime, h.isDeleted, h.longitude, h.latitude, " +
            "cf.complementaryFacilityId, cf.hasFridge, cf.hasBed, cf.hasWasher, cf.hasWaterHeater, " +
            "cf.hasWifi, cf.hasSofa, cf.hasKitchen, cf.hasRangeHood, cf.hasGasStove, cf.hasTV, " +
            "cf.hasAirConditioner, cf.hasCloset, cf.hasSmartDoorLock, cf.hasBalcony, cf.hasCentralHeating, " +
            "cf.hasCupBoard, cf.hasDiningTable, " +
            "hm.picPath1, hm.picPath2, hm.picPath3, hm.picPath4, hm.picPath5, " +
            "hm.picPath6, hm.picPath7, hm.picPath8, hm.picPath9, hm.picPath10, " +
            "COALESCE(u1.phone, u2.phone) as phone, " +
            "CASE " +
            "    WHEN h.houseRentType = 2 THEN i1.realName " + // 中介代理
            "    ELSE i2.realName " + // 房东直售
            "END as realname " +
            "FROM house h " +
            "LEFT JOIN complementaryfacilities cf ON h.complementaryFacilityId = cf.complementaryFacilityId " +
            "LEFT JOIN housemedia hm ON h.houseId = hm.houseId " +
            "LEFT JOIN agent a ON h.agentId = a.agentId " +
            "LEFT JOIN user u1 ON a.userId = u1.userId " +
            "LEFT JOIN identityinfo i1 ON u1.userId = i1.userid " +
            "LEFT JOIN landlord l ON h.landLordId = l.landlordId " +
            "LEFT JOIN user u2 ON l.userId = u2.userId " +
            "LEFT JOIN identityinfo i2 ON u2.userId = i2.userid " +
            "WHERE h.houseId = #{houseId} AND h.isDeleted = false")
    @Results({
            @Result(property = "houseId", column = "houseId"),
            @Result(property = "landlord.landlordId", column = "landLordId"),
            @Result(property = "houseSummary", column = "houseSummary"),
            @Result(property = "houseTitle", column = "houseTitle"),
            @Result(property = "agent.agentId", column = "agentId"),
            @Result(property = "complementaryFacilities.complementaryFacilityId", column = "complementaryFacilityId"),
            @Result(property = "complementaryFacilities.hasFridge", column = "hasFridge"),
            @Result(property = "complementaryFacilities.hasBed", column = "hasBed"),
            @Result(property = "complementaryFacilities.hasWasher", column = "hasWasher"),
            @Result(property = "complementaryFacilities.hasWaterHeater", column = "hasWaterHeater"),
            @Result(property = "complementaryFacilities.hasWifi", column = "hasWifi"),
            @Result(property = "complementaryFacilities.hasSofa", column = "hasSofa"),
            @Result(property = "complementaryFacilities.hasKitchen", column = "hasKitchen"),
            @Result(property = "complementaryFacilities.hasRangeHood", column = "hasRangeHood"),
            @Result(property = "complementaryFacilities.hasGasStove", column = "hasGasStove"),
            @Result(property = "complementaryFacilities.hasTV", column = "hasTV"),
            @Result(property = "complementaryFacilities.hasAirConditioner", column = "hasAirConditioner"),
            @Result(property = "complementaryFacilities.hasCloset", column = "hasCloset"),
            @Result(property = "complementaryFacilities.hasSmartDoorLock", column = "hasSmartDoorLock"),
            @Result(property = "complementaryFacilities.hasBalcony", column = "hasBalcony"),
            @Result(property = "complementaryFacilities.hasCentralHeating", column = "hasCentralHeating"),
            @Result(property = "complementaryFacilities.hasCupBoard", column = "hasCupBoard"),
            @Result(property = "complementaryFacilities.hasDiningTable", column = "hasDiningTable"),
            @Result(property = "propertyVerification.propertyVerifyId", column = "propertyVerifyId"),
            @Result(property = "houseLocation", column = "houseLocation"),
            @Result(property = "layout", column = "layout"),
            @Result(property = "area", column = "area"),
            @Result(property = "depositType", column = "depositType"),
            @Result(property = "floor", column = "floor"),
            @Result(property = "orientation", column = "orientation"),
            @Result(property = "status", column = "status"),
            @Result(property = "rent", column = "rent"),
            @Result(property = "paymentType", column = "paymentType"),
            @Result(property = "houseRentType", column = "houseRentType"),
            @Result(property = "subwayInfo", column = "subwayInfo"),
            @Result(property = "createTime", column = "createTime"),
            @Result(property = "isDeleted", column = "isDeleted"),
            @Result(property = "longitude", column = "longitude"),
            @Result(property = "latitude", column = "latitude"),
            @Result(property = "phone", column = "phone"),
            @Result(property = "houseMedia.houseId", column = "houseId"),
            @Result(property = "houseMedia.picPath1", column = "picPath1"),
            @Result(property = "houseMedia.picPath2", column = "picPath2"),
            @Result(property = "houseMedia.picPath3", column = "picPath3"),
            @Result(property = "houseMedia.picPath4", column = "picPath4"),
            @Result(property = "houseMedia.picPath5", column = "picPath5"),
            @Result(property = "houseMedia.picPath6", column = "picPath6"),
            @Result(property = "houseMedia.picPath7", column = "picPath7"),
            @Result(property = "houseMedia.picPath8", column = "picPath8"),
            @Result(property = "houseMedia.picPath9", column = "picPath9"),
            @Result(property = "houseMedia.picPath10", column = "picPath10"),
            @Result(property = "realName", column = "realname") // 新增字段
    })
    House selectHouseDetailById(Long houseId);

    @Insert("insert into house(houseTitle,landLordId,houseSummary,complementaryFacilityId, propertyVerifyId, houseLocation, layout, floor, orientation, status,rent,paymentType,houseRentType,subwayInfo,createTime,isDeleted,phone,depositType,longitude,latitude) " +
            "values(#{houseTitle},#{landlord.landlordId},#{houseSummary},#{complementaryFacilities.complementaryFacilityId}, #{propertyVerification.propertyVerifyId}, #{houseLocation}, #{layout}, #{floor}, #{orientation},#{status},#{rent},#{paymentType},#{houseRentType},#{subwayInfo},#{createTime},#{isDeleted},#{phone},#{depositType},#{longitude},#{latitude})")
    void insertHouse(House house);

    // 插入房产认证信息（待审核状态）
    @Insert("insert into propertyVerification(userId, propertyPicPath, propertyNo, " +
            "propertyAddr, verifyStatus, isDeleted, applyTime) " +
            "values(#{userId}, #{propertyPicPath}, #{propertyNo}, " +
            "#{propertyAddr}, #{verifyStatus}, #{isDeleted}, #{applyTime})") // 0=待审核
    void insertPropertyVerification(PropertyVerification propertyVerification);

    /**
     * 根据房产认证id查询房产认证信息
     * @param propertyVerifyId 房产认证id
     * @return 房产认证信息
     */
    @Select("select * from propertyVerification where propertyVerifyId = #{propertyVerifyId} and isDeleted=0")
    PropertyVerification selectPropertyVerificationById(Long propertyVerifyId);


    /**
     * 添加配套设施
     * @param complementaryFacilities 配套设施
     */
    @Insert("insert into complementaryFacilities(hasFridge,hasBed,hasWasher,hasWaterHeater,hasWifi,hasSofa,hasKitchen,hasRangeHood,hasGasStove,hasTV,hasAirConditioner,hasCloset,hasSmartDoorLock,hasBalcony,hasCentralHeating,hasCupBoard,hasDiningTable) " +
            "values(#{hasFridge}, #{hasBed}, #{hasWasher}, #{hasWaterHeater}, #{hasWifi}, #{hasSofa}, #{hasKitchen}, #{hasRangeHood}, #{hasGasStove}, #{hasTV}, #{hasAirConditioner}, #{hasCloset}, #{hasSmartDoorLock}, #{hasBalcony}, #{hasCentralHeating}, #{hasCupBoard}, #{hasDiningTable})")
    void insertComplementaryFacilities(ComplementaryFacilities complementaryFacilities);

    /**
     * 根据配套设施条件查询配套设施id
     * @param condition 配套设施条件
     * @return 配套设施id
     */
    @Select("<script>" +
            "SELECT complementaryFacilityId FROM complementaryfacilities WHERE 1=1" +
            "<if test='hasFridge != null'> AND hasFridge = #{hasFridge}</if>" +
            "<if test='hasBed != null'> AND hasBed = #{hasBed}</if>" +
            "<if test='hasWasher != null'> AND hasWasher = #{hasWasher}</if>" +
            "<if test='hasWaterHeater != null'> AND hasWaterHeater = #{hasWaterHeater}</if>" +
            "<if test='hasWifi != null'> AND hasWifi = #{hasWifi}</if>" +
            "<if test='hasSofa != null'> AND hasSofa = #{hasSofa}</if>" +
            "<if test='hasKitchen != null'> AND hasKitchen = #{hasKitchen}</if>" +
            "<if test='hasRangeHood != null'> AND hasRangeHood = #{hasRangeHood}</if>" +
            "<if test='hasGasStove != null'> AND hasGasStove = #{hasGasStove}</if>" +
            "<if test='hasTV != null'> AND hasTV = #{hasTV}</if>" +
            "<if test='hasAirConditioner != null'> AND hasAirConditioner = #{hasAirConditioner}</if>" +
            "<if test='hasCloset != null'> AND hasCloset = #{hasCloset}</if>" +
            "<if test='hasSmartDoorLock != null'> AND hasSmartDoorLock = #{hasSmartDoorLock}</if>" +
            "<if test='hasBalcony != null'> AND hasBalcony = #{hasBalcony}</if>" +
            "<if test='hasCentralHeating != null'> AND hasCentralHeating = #{hasCentralHeating}</if>" +
            "<if test='hasCupBoard != null'> AND hasCupBoard = #{hasCupBoard}</if>" +
            "<if test='hasDiningTable != null'> AND hasDiningTable = #{hasDiningTable}</if>" +
            "order by complementaryFacilityId desc LIMIT 1" +
            "</script>")
    Long selectComplementaryFacilitiesIdByCondition(ComplementaryFacilities condition);

    /*
     * 查询用户所有房产认证信息
     */
    @Select("select propertyVerifyId,propertyNo,propertyPicPath,propertyAddr,verifyStatus,isDeleted,applyTime from propertyVerification where userId=#{userId} and isDeleted=0")
    List<PropertyVerification> selectPropertyVerificationByUserId(Long userId);


    /*
     * 查询房东所有房产信息
     */
    @Select("select housetitle, houseId,landLordId `landlord.landlordId`,agentId `agent.agentId`,houseSummary,complementaryFacilityId  `complementaryFacilities.complementaryFacilityId`, propertyVerifyId `propertyVerification.propertyVerifyId`, houseLocation, layout, floor, orientation, status,rent,paymentType,houseRentType,subwayInfo,createTime,phone,area from house where landlordId=#{landlordId} and isDeleted=0")
    List<House> selectHouseByLandLordId(Long landlordId);

    /*
    * 查询房产认证信息是否存在
     */
    @Select("select count(1) from propertyVerification where propertyNo = #{propertyNo} and verifyStatus !=2 and isDeleted=0")
    int selectByPropertyNo(String propertyNo);

    /*
    * 验证房产信息是否存在
     */
    @Select("select count(1) from house where propertyVerifyId = #{propertyVerifyId} and isDeleted=0")
    int selectByPropertyVerifyId(Long propertyVerifyId);

    /*
    * 软删除标记已被驳回的房产认证信息
     */
    @Update("update propertyVerification set isDeleted=1 where propertyVerifyId=#{propertyVerifyId} and verifyStatus in (2,3) ")
    void updatePropertyVerificationDeleted(Long propertyVerifyId);

    /*
    * 批量软删除标记已被驳回的房产认证信息
     */
    @Update({"<script>",
            "UPDATE propertyVerification",
            "SET isDeleted = 1",
            "WHERE propertyVerifyId IN",
            "<foreach item='id' collection='propertyVerifyIds' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "AND verifyStatus = 2",
            "</script>"})
    void updateBatchPropertyVerificationDeleted(@Param("propertyVerifyIds") List<Long> propertyVerifyIds);

    /*
    *补充房源信息
     */
    @Update("<script>" +
            "UPDATE house " +
            "<set>" +
            "<if test='houseTitle != null'>houseTitle = #{houseTitle},</if>" +
            "<if test='complementaryFacilities != null'>complementaryFacilities = #{complementaryFacilities.complementaryFacilityId},</if>" +
            "<if test='houseSummary != null'>houseSummary = #{houseSummary},</if>" +
            "<if test='houseLocation != null'>houseLocation = #{houseLocation},</if>" +
            "<if test='layout != null'>layout = #{layout},</if>" +
            "<if test='floor != null'>floor = #{floor},</if>" +
            "<if test='orientation != null'>orientation = #{orientation},</if>" +
            "<if test='rent != null'>rent = #{rent},</if>" +
            "<if test='paymentType != null'>paymentType = #{paymentType},</if>" +
            "<if test='subwayInfo != null'>subwayInfo = #{subwayInfo},</if>" +
            "<if test='phone != null'>phone = #{phone},</if>" +
            "<if test='depositType != null'>depositType = #{depositType},</if>" +
            "<if test='area != null'>area = #{area},</if>" +
            "</set>" +
            "WHERE houseId = #{houseId}" +
            "</script>")
    void updateHouseInfo(House house);

    /**
     * 根据房产id查询房产信息
     * @param houseId 房产id
     * @return 房产信息
     */
    @Select("SELECT landLordId `landlord.landlordId`,houseSummary,complementaryFacilityId `complementaryFacilities.complementaryFacilityId`, propertyVerifyId `propertyVerification.propertyVerifyId`, houseLocation, layout, floor, orientation, status,rent,paymentType,houseRentType,subwayInfo,createTime,isDeleted,phone,depositType,longitude,latitude FROM house WHERE houseId = #{houseId}")
    House selectHouseById(Long houseId);

    /**
     * 更新配套设施信息
     * @param complementaryFacilities 配套设施
     */
    @Update("<script>" +
            "UPDATE complementaryFacilities " +
            "<set>" +
            "<if test='hasFridge != null'>hasFridge = #{hasFridge},</if>" +
            "<if test='hasBed != null'>hasBed = #{hasBed},</if>" +
            "<if test='hasWasher != null'>hasWasher = #{hasWasher},</if>" +
            "<if test='hasWaterHeater != null'>hasWaterHeater = #{hasWaterHeater},</if>" +
            "<if test='hasWifi != null'>hasWifi = #{hasWifi},</if>" +
            "<if test='hasSofa != null'>hasSofa = #{hasSofa},</if>" +
            "<if test='hasKitchen != null'>hasKitchen = #{hasKitchen},</if>" +
            "<if test='hasRangeHood != null'>hasRangeHood = #{hasRangeHood},</if>" +
            "<if test='hasGasStove != null'>hasGasStove = #{hasGasStove},</if>" +
            "<if test='hasTV != null'>hasTV = #{hasTV},</if>" +
            "<if test='hasAirConditioner != null'>hasAirConditioner = #{hasAirConditioner},</if>" +
            "<if test='hasCloset != null'>hasCloset = #{hasCloset},</if>" +
            "<if test='hasSmartDoorLock != null'>hasSmartDoorLock = #{hasSmartDoorLock},</if>" +
            "<if test='hasBalcony != null'>hasBalcony = #{hasBalcony},</if>" +
            "<if test='hasCentralHeating != null'>hasCentralHeating = #{hasCentralHeating},</if>" +
            "<if test='hasCupBoard != null'>hasCupBoard = #{hasCupBoard},</if>" +
            "<if test='hasDiningTable != null'>hasDiningTable = #{hasDiningTable},</if>" +
            "</set>" +
            "WHERE complementaryFacilityId = #{complementaryFacilityId}" +
            "</script>")
    void updateComplementaryFacilities(ComplementaryFacilities complementaryFacilities);


    /**
     * 根据房产编号查询房产认证状态
     * @param propertyNo
     * @return0=待审核，1=通过，2=驳回,3=已取消
     */
    @Select("select verifyStatus from propertyverification where propertyNo=#{propertyNo} and isDeleted=0")
    Integer selectPropertyVerifyStatusByPropertyNo(String propertyNo);

    /**
     * 根据房产编号查询房产认证驳回原因
     * @param propertyNo 房产编号
     * @return 房产认证驳回原因
     */
    @Select("select refuseReason from propertyverification where propertyNo=#{propertyNo} and isDeleted=0 and verifyStatus=2")
    String selectPropertyVerifyRefuseReasonByPropertyNo(String propertyNo);

    /**
     * 取消房产认证
     * @param propertyNo 房产证编号
     */
    @Update("update propertyverification set verifyStatus=3 where propertyNo=#{propertyNo} and isDeleted=0")
    void updateCancelPropertyVerifyByPropertyNo(String propertyNo);

    /**
     * 根据代理类型获取房源信息
     * @return
     */
    @Select("SELECT * FROM house WHERE houseRentalType = #{houseRentalType}")
    List<House> selectAllHouseByType(Integer houseRentalType);



    /**
     * 房东下架或重新上架房源
     * @param houseId 房源id
     * @param status 房源状态 0=上架重新为待出租状态，2=下架
     */
    @Update("update house set status = #{status} where houseId = #{houseId}")
    void updateHouseStatus(@Param("houseId") Long houseId, @Param("status") Integer status);

    /**
     * 根据房源ID查询房源信息
     * @param houseId 房源ID
     * @return 房源信息
     */
    @Select("select housetitle, houseId,landLordId `landlord.landlordId`,agentId `agent.agentId`,houseSummary,complementaryFacilityId  `complementaryFacilities.complementaryFacilityId`, propertyVerifyId `propertyVerification.propertyVerifyId`, houseLocation, layout, floor, orientation, status,rent,paymentType,houseRentType,subwayInfo,createTime,phone,area from house where houseId=#{houseId} and isDeleted=0")
    House selectHouseByHouseId(Long houseId);

    /**
     * 获取所有可代理的房源（出租类型为中介代理且未被代理的房源）
     */
    @Select("SELECT h.houseId, h.landLordId, h.houseSummary, h.houseTitle, h.agentId, " +
            "h.houseLocation, h.layout, h.area, h.floor, h.orientation, h.status, h.rent, " +
            "h.paymentType, h.houseRentType, h.subwayInfo, h.createTime, h.phone, " +
            "hm.picPath1, hm.picPath2, hm.picPath3, hm.picPath4, hm.picPath5 " +
            "FROM house h " +
            "LEFT JOIN housemedia hm ON h.houseId = hm.houseId " +
            "WHERE h.agentId IS NULL AND h.status = 0 AND h.isDeleted = 0")
    @Results({
        @Result(property = "houseId", column = "houseId"),
        @Result(property = "houseMedia.picPath1", column = "picPath1"),
        @Result(property = "houseMedia.picPath2", column = "picPath2"),
        @Result(property = "houseMedia.picPath3", column = "picPath3"),
        @Result(property = "houseMedia.picPath4", column = "picPath4"),
        @Result(property = "houseMedia.picPath5", column = "picPath5")
    })
    List<House> selectAvailableAgentHouses();

    /**
     * 根据中介ID查询申请记录
     */
    @Select("SELECT aha.applicationId, aha.agentId, aha.houseId, aha.applicationReason, " +
            "aha.applicationTime, aha.status, aha.refuseReason, aha.handleTime, " +
            "h.houseTitle, h.houseSummary, h.houseLocation, h.rent as houseRent " +
            "FROM agenthouseapplication aha " +
            "LEFT JOIN house h ON aha.houseId = h.houseId " +
            "WHERE aha.agentId = #{agentId} " +
            "ORDER BY aha.applicationTime DESC")
    List<AgentHouseApplication> selectAgentApplicationsByAgentId(Long agentId);

    /**
     * 检查中介是否已经申请过该房源
     */
    @Select("SELECT COUNT(*) FROM agenthouseapplication " +
            "WHERE houseId = #{houseId} AND agentId = #{agentId} AND status IN (0, 1)")
    int checkExistingApplication(@Param("houseId") Long houseId, @Param("agentId") Long agentId);

    /**
     * 取消申请
     */
    @Update("UPDATE agenthouseapplication SET status = 3 WHERE applicationId = #{applicationId}")
    void cancelAgentApplication(Long applicationId);

    /**
     * 根据中介ID获取所有代理的房源
     */
    @Select("SELECT h.houseId, h.landLordId, h.houseSummary, h.houseTitle, h.agentId, " +
            "h.houseLocation, h.layout, h.area, h.floor, h.orientation, h.status, h.rent, " +
            "h.paymentType, h.houseRentType, h.subwayInfo, h.createTime, h.phone " +
            "FROM house h " +
            "WHERE h.agentId = #{agentId} AND h.isDeleted = 0")
    List<House> selectAllAgentHouseByAgentId(Long agentId);

    /**
     * 根据中介ID获取正在代理的房源（状态为待出租）
     */
    @Select("SELECT h.houseId, h.landLordId, h.houseSummary, h.houseTitle, h.agentId, " +
            "h.houseLocation, h.layout, h.area, h.floor, h.orientation, h.status, h.rent, " +
            "h.paymentType, h.houseRentType, h.subwayInfo, h.createTime, h.phone " +
            "FROM house h " +
            "WHERE h.agentId = #{agentId} AND h.status = 0 AND h.isDeleted = 0")
    List<House> selectAgentingHouseByAgentId(Long agentId);

    /**
     * 根据中介ID获取已出租的房源（状态为已出租）
     */
    @Select("SELECT h.houseId, h.landLordId, h.houseSummary, h.houseTitle, h.agentId, " +
            "h.houseLocation, h.layout, h.area, h.floor, h.orientation, h.status, h.rent, " +
            "h.paymentType, h.houseRentType, h.subwayInfo, h.createTime, h.phone " +
            "FROM house h " +
            "WHERE h.agentId = #{agentId} AND h.status = 1 AND h.isDeleted = 0")
    List<House> selectRentedHouseByAgentId(Long agentId);

    /**
     * 插入中介房源代理申请
     */
    @Insert("INSERT INTO agenthouseapplication (agentId, houseId, applicationReason, applicationTime, status) " +
            "VALUES (#{agentId}, #{houseId}, #{applicationReason}, NOW(), 0)")
    void insertAgentHouseApplication(@Param("agentId") Long agentId,
                                   @Param("houseId") Long houseId,
                                   @Param("applicationReason") String applicationReason);

    /**
     * 根据房东ID获取代理申请列表
     */
    @Select("SELECT aha.applicationId, aha.agentId, aha.houseId, aha.applicationReason, " +
            "aha.applicationTime, aha.status, aha.refuseReason, aha.handleTime, " +
            "av.realName as agentName, av.companyName as agentCompany, av.phone as agentPhone, " +
            "h.houseTitle, h.houseSummary, h.houseLocation, h.rent as houseRent " +
            "FROM agenthouseapplication aha " +
            "LEFT JOIN agentverification av ON aha.agentId = av.userId " +
            "LEFT JOIN house h ON aha.houseId = h.houseId " +
            "WHERE h.landLordId = #{landlordId} " +
            "ORDER BY aha.applicationTime DESC")
    List<AgentHouseApplication> selectAgentApplicationsByLandlordId(Long landlordId);

    /**
     * 根据房东ID获取待处理的代理申请
     */
    @Select("SELECT aha.applicationId, aha.agentId, aha.houseId, aha.applicationReason, " +
            "aha.applicationTime, aha.status, aha.refuseReason, aha.handleTime, " +
            "av.realName as agentName, av.companyName as agentCompany, av.phone as agentPhone, " +
            "h.houseTitle, h.houseSummary, h.houseLocation, h.rent as houseRent " +
            "FROM agenthouseapplication aha " +
            "LEFT JOIN agentverification av ON aha.agentId = av.userId " +
            "LEFT JOIN house h ON aha.houseId = h.houseId " +
            "WHERE h.landLordId = #{landlordId} AND aha.status = 0 " +
            "ORDER BY aha.applicationTime ASC")
    List<AgentHouseApplication> selectPendingAgentApplicationsByLandlordId(Long landlordId);

    /**
     * 更新代理申请状态
     */
    @Update("UPDATE agenthouseapplication SET status = #{status}, refuseReason = #{refuseReason}, " +
            "handleTime = NOW() WHERE applicationId = #{applicationId}")
    void updateAgentApplicationStatus(@Param("applicationId") Long applicationId,
                                    @Param("status") Integer status,
                                    @Param("refuseReason") String refuseReason);

    /**
     * 根据申请ID获取申请详情
     */
    @Select("SELECT aha.applicationId, aha.agentId, aha.houseId, aha.applicationReason, " +
            "aha.applicationTime, aha.status, aha.refuseReason, aha.handleTime, " +
            "av.realName as agentName, av.companyName as agentCompany, av.phone as agentPhone, " +
            "h.houseTitle, h.houseSummary, h.houseLocation, h.rent as houseRent " +
            "FROM agenthouseapplication aha " +
            "LEFT JOIN agentverification av ON aha.agentId = av.userId " +
            "LEFT JOIN house h ON aha.houseId = h.houseId " +
            "WHERE aha.applicationId = #{applicationId}")
    AgentHouseApplication selectAgentApplicationById(Long applicationId);

    /**
     * 更新房源的代理中介
     */
    @Update("UPDATE house SET agentId = #{agentId} WHERE houseId = #{houseId}")
    void updateHouseAgent(@Param("houseId") Long houseId, @Param("agentId") Long agentId);

    /**
     * 根据配套设施ID查询配套设施信息
     * @param id 配套设施ID
     * @return 配套设施信息
     */
    @Select("select * from complementaryfacilities where complementaryFacilityId=#{id}")
    ComplementaryFacilities selectComplementaryFacilitiesById(Long id);
}
